using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Windows.Forms;
using System.Configuration;
namespace DailyTransaction
{
    class DAO
    {
        private string connectionString;
        private SqlConnection con;        
        public bool connect()
        {
            //connectionString = ConfigurationManager.ConnectionStrings["SQLServer"].ConnectionString;
            connectionString = GlobalClass.getConnectionString();
            if (GlobalClass.errmsg != "")
            {
                MessageBox.Show(GlobalClass.errmsg);
                return false;
            }
            try
            {
                if (con == null)
                {
                    con = new SqlConnection(connectionString);
                    con.Open();
                }
                else if (con.State == ConnectionState.Closed)
                    con.Open();
            }
            catch (SqlException se)
            {
                MessageBox.Show(se.Message);
                return false;
            }
            return true;
            
        }
        public void disconnect()
        {
            try
            {
                if (con.State == ConnectionState.Open)
                    con.Close();
            }
            catch (SqlException se)
            { }
            finally {
                con.Close();
            }
        }

        public void executeNonquery(string sql)
        {
            if (connect() == false)
                return;
            SqlCommand cmd = new SqlCommand(sql);
            cmd.Connection = con;
            SqlTransaction transaction = con.BeginTransaction();            
            cmd.Transaction = transaction;
            try
            {
                cmd.ExecuteNonQuery();
                transaction.Commit();
            }catch(SqlException se)
            {
                transaction.Rollback();
            }
            
            disconnect();
        }

        public DataTable executeQuery(string sql)
        {
            if (connect() == false)
                return null;
            //connect();
            //MessageBox.Show(sql);
            SqlDataAdapter adap = new SqlDataAdapter(sql, con);
            DataTable tbl = new DataTable();
            adap.Fill(tbl);
            disconnect();
            //MessageBox.Show(tbl.Rows.Count.ToString());
            return tbl;
        }

        public int exec_proc(string proc_name, string fromdate, string todate, string cmscenter)
        {
            if (connect() == false)
                return 0;
            int nr=0;
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = proc_name;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@DateFrom", fromdate);
            cmd.Parameters.AddWithValue("@DateTo", todate);
            cmd.Parameters.AddWithValue("@CMSCenter", cmscenter);
            cmd.Parameters["@DateFrom"].DbType = DbType.DateTime;
            cmd.Parameters["@DateTo"].DbType = DbType.DateTime;
            cmd.Parameters["@CMSCenter"].DbType = DbType.String;
            cmd.Connection = con;
            SqlTransaction trans = con.BeginTransaction();
            cmd.Transaction = trans;
            try
            {
                nr = cmd.ExecuteNonQuery();
                trans.Commit();
            }
            catch (SqlException se)
            {
                trans.Rollback();
            }
            
            disconnect();
            return nr;
        }
        public int exec_proc(string proc_name, string fromdate, string todate)
        {
            if (connect() == false)
                return 0;
            int nr = 0;
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = proc_name;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@DateFrom", fromdate);
            cmd.Parameters.AddWithValue("@DateTo", todate);
            cmd.Parameters["@DateFrom"].DbType = DbType.DateTime;
            cmd.Parameters["@DateTo"].DbType = DbType.DateTime;
            cmd.Connection = con;
            //added on 21/09/2009
            cmd.CommandTimeout = 0;
            //end
            SqlTransaction trans = con.BeginTransaction();
            cmd.Transaction = trans;
            
            try
            {
                nr = cmd.ExecuteNonQuery();
                trans.Commit();
            }
            catch (SqlException se)
            {
                MessageBox.Show("Error: " + se.Message);
                trans.Rollback();
            }
            disconnect();
            return nr;
        }

        public void Delete_temp_revenue()
        {
            //exec_proc("sp_zDelete_temp_Revenue");
            if (connect() == false)
                return;
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "sp_zDelete_temp_Revenue";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = con;
            SqlTransaction trans = con.BeginTransaction();
            cmd.Transaction = trans;
            try
            {
                cmd.ExecuteNonQuery();
                trans.Commit();
            }
            catch (SqlException se)
            {
                trans.Rollback();
            }
            disconnect();
        }

        internal void Delete_temp_Proc_Phar()
        {
            //exec_proc( "sp_zDelete_temp_Proc_Phar");
            if (connect() == false)
                return;
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "sp_zDelete_temp_Proc_Phar";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = con;
            SqlTransaction trans = con.BeginTransaction();
            cmd.Transaction = trans;
            try
            {
                cmd.ExecuteNonQuery();
                trans.Commit();
            }
            catch (SqlException se)
            {
                trans.Rollback();
            }
            disconnect();
        }

        internal int Duplicate_curr_Revenue_Direct(string fromdate, string todate, string cmscenter)
        {
            return exec_proc("sp_Duplicate_curr_Revenue_Direct", fromdate, todate, cmscenter);
        }

        internal int Duplicate_Revenuey(string fromdate, string todate, string cmscenter)
        {
            return exec_proc("sp_Duplicate_Revenuey", fromdate, todate, cmscenter);            
        }

        internal int Duplicate_Revenuey_Direct(string fromdate, string todate, string cmscenter)
        {
            return exec_proc("sp_Duplicate_Revenuey_Direct", fromdate, todate, cmscenter);            
        }

        internal int Duplicate_Revenuev(string fromdate, string todate, string cmscenter)
        {
            return exec_proc("sp_Duplicate_Revenuev", fromdate, todate, cmscenter);
        }

        internal int Duplicate_curr_Revenue(string fromdate, string todate, string cmscenter)
        {
            return exec_proc("sp_Duplicate_curr_Revenue", fromdate, todate, cmscenter);
        }

        internal int Duplicate_Act_proy(string fromdate, string todate, string cmscenter)
        {
            return exec_proc("sp_Duplicate_Act_proy", fromdate, todate, cmscenter);
        }

        internal int Duplicate_Act_prov(string fromdate, string todate, string cmscenter)
        {
            return exec_proc("sp_Duplicate_Act_prov", fromdate, todate, cmscenter);

        }

        internal int Duplicate_Curr_Act_proc(string fromdate, string todate, string cmscenter)
        {
            return exec_proc("sp_Duplicate_Curr_Act_proc", fromdate, todate, cmscenter);
        }

        internal int Duplicate_curr_Act_phar(string fromdate, string todate, string cmscenter)
        {
            return exec_proc("sp_Duplicate_curr_Act_phar", fromdate, todate, cmscenter);
        }

        internal int Duplicate_Act_phay(string fromdate, string todate, string cmscenter)
        {
            return exec_proc("sp_Duplicate_Act_phay", fromdate, todate, cmscenter);
        }

        internal int Duplicate_Act_phav(string fromdate, string todate, string cmscenter)
        {
            return exec_proc("sp_Duplicate_Act_phav", fromdate, todate, cmscenter);
        }

        internal void Batch_update_Invoice_type()
        {
            //exec_proc("sp_Batch_update_Invoice_type");
            if (connect() == false)
                return;
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "sp_Batch_update_Invoice_type";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = con;
            SqlTransaction trans = con.BeginTransaction();
            cmd.Transaction = trans;
            try
            {
                cmd.ExecuteNonQuery();
                trans.Commit();
            }
            catch (SqlException se)
            {
                trans.Rollback();
            }
            disconnect();
        }

        internal void Batch_Update_Doc_code()
        {
            if (connect() == false)
                return;
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "sp_Batch_update_Doc_code";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = con;
            SqlTransaction trans = con.BeginTransaction();
            cmd.Transaction = trans;
            try
            {
                cmd.ExecuteNonQuery();
                trans.Commit();
            }
            catch (SqlException se)
            {
                trans.Rollback();
            }
            disconnect();
        }

        internal void Append_Rev2Table()
        {
            if (connect() == false)
                return;
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "sp_Append_Rev2Table";
            //cmd.CommandText = "sp_Append_Rev2Table_VN";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = con;
            SqlTransaction trans = con.BeginTransaction();
            cmd.Transaction = trans;
            try
            {
                cmd.ExecuteNonQuery();
                trans.Commit();
            }
            catch (SqlException se)
            {
                trans.Rollback();
            }
            disconnect();
        }

        internal void Append_Proc_Phar2Table()
        {
            if (connect() == false)
                return;
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "sp_Append_Proc_Phar2Table";
            //cmd.CommandText = "sp_Append_Proc_Phar2Table_VN";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = con;
            SqlTransaction trans = con.BeginTransaction();
            cmd.Transaction = trans;
            try
            {
                cmd.ExecuteNonQuery();
                trans.Commit();
            }
            catch (SqlException se)
            {
                trans.Rollback();
            }
            disconnect();
        }

        internal int qUpdate_CCP(string fromdate, string todate)
        {
            if (connect() == false)
                return 0;
            int nr = 0;
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "sp_qUpdate_CCP";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@DateFrom", fromdate);
            cmd.Parameters.AddWithValue("@DateTo", todate);
            cmd.Parameters["@DateFrom"].DbType = DbType.DateTime;
            cmd.Parameters["@DateTo"].DbType = DbType.DateTime;
            cmd.Connection = con;
            SqlTransaction trans = con.BeginTransaction();
            cmd.Transaction = trans;
            try
            {
                nr = cmd.ExecuteNonQuery();
                trans.Commit();
            }
            catch (SqlException se)
            {
                trans.Rollback();
            }
            disconnect();
            return nr;
        }

        internal int qAppend_VAT_CMS_NUM(string fromdate, string todate )
        {
            if (connect() == false)
                return 0;
            int nr = 0;
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "sp_qAppend_VAT_CMS_NUM";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@DateFrom", fromdate);
            cmd.Parameters.AddWithValue("@DateTo", todate);
            cmd.Parameters["@DateFrom"].DbType = DbType.DateTime;
            cmd.Parameters["@DateTo"].DbType = DbType.DateTime;
            cmd.Connection = con;
            SqlTransaction trans = con.BeginTransaction();
            cmd.Transaction = trans;
            try
            {
                nr = cmd.ExecuteNonQuery();
                trans.Commit();
            }
            catch (SqlException se)
            {
                trans.Rollback();
            }
            disconnect();
            return nr;
        }

        internal int qUpdate_PayAccountCode_AC_GTE(string fromdate, string todate)
        {
            if (connect() == false)
                return 0;
            int nr = 0;
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "sp_qUpdate_PayAccountCode_AC_GTE";
            //cmd.CommandText = "sp_qUpdate_PayAccountCode_AC_GTE_VN";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@DateFrom", fromdate);
            cmd.Parameters.AddWithValue("@DateTo", todate);
            cmd.Parameters["@DateFrom"].DbType = DbType.DateTime;
            cmd.Parameters["@DateTo"].DbType = DbType.DateTime;
            cmd.Connection = con;
            SqlTransaction trans = con.BeginTransaction();
            cmd.Transaction = trans;
            try
            {
                nr = cmd.ExecuteNonQuery();
                trans.Commit();
            }
            catch (SqlException se)
            {
                trans.Rollback();
            }
            disconnect();
            return nr;
        }

        internal int qUpdate_PayAccountCode_OUTSTD(string fromdate, string todate)
        {
            if (connect() == false)
                return 0;
            int nr = 0;
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "sp_qUpdate_PayAccountCode_OUTSTD";
            //cmd.CommandText = "sp_qUpdate_PayAccountCode_OUTSTD_VN";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@DateFrom", fromdate);
            cmd.Parameters.AddWithValue("@DateTo", todate);
            cmd.Parameters["@DateFrom"].DbType = DbType.DateTime;
            cmd.Parameters["@DateTo"].DbType = DbType.DateTime;
            cmd.Connection = con;
            SqlTransaction trans = con.BeginTransaction();
            cmd.Transaction = trans;
            try
            {
                nr = cmd.ExecuteNonQuery();
                trans.Commit();
            }
            catch (SqlException se)
            {
                trans.Rollback();
            }
            disconnect();
            return nr;
        }

        internal void qDelete_AV_INV_To_Temp_VAT_Void()
        {
            string sql = "DELETE tTemp_VAT_Void";
            if (connect() == false)
                return;
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = sql;            
            cmd.Connection = con;
            SqlTransaction trans = con.BeginTransaction();
            cmd.Transaction = trans;
            try
            {
                cmd.ExecuteNonQuery();
                trans.Commit();
            }
            catch (SqlException se)
            {
                trans.Rollback();
            }
            disconnect();

        }

        internal int qAppend_AV_INV_To_Temp_VAT_Void(string fromdate, string todate)
        {
            if (connect() == false)
                return 0;
            int nr = 0;
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "sp_qAppend_AV_INV_To_Temp_VAT_Void";
            //cmd.CommandText = "sp_qAppend_AV_INV_To_Temp_VAT_Void_VN";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@DateFrom", fromdate);
            cmd.Parameters.AddWithValue("@DateTo", todate);
            cmd.Parameters["@DateFrom"].DbType = DbType.DateTime;
            cmd.Parameters["@DateTo"].DbType = DbType.DateTime;
            cmd.Connection = con;
            SqlTransaction trans = con.BeginTransaction();
            cmd.Transaction = trans;
            try
            {
                nr = cmd.ExecuteNonQuery();
                trans.Commit();
            }
            catch (SqlException se)
            {
                trans.Rollback();
            }
            disconnect();
            return nr;
        }

        internal int Update_ACC_Void_Inv(string fromdate, string todate)
        {
            if (connect() == false)
                return 0;
            int nr = 0;
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "sp_Update_ACC_Void_Inv";
            //cmd.CommandText = "sp_Update_ACC_Void_Inv_VN";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@DateFrom", fromdate);
            cmd.Parameters.AddWithValue("@DateTo", todate);
            cmd.Parameters["@DateFrom"].DbType = DbType.DateTime;
            cmd.Parameters["@DateTo"].DbType = DbType.DateTime;
            cmd.Connection = con;
            SqlTransaction trans = con.BeginTransaction();
            cmd.Transaction = trans;
            try
            {
                nr = cmd.ExecuteNonQuery();
                trans.Commit();
            }
            catch (SqlException se)
            {
                trans.Rollback();
            }
            disconnect();
            return nr;
        }

        internal void Update_totals_temp_revenue(string CMSCenter)
        {
            if (connect() == false)
                return;
            int nr = 0;
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "sp_Update_totals_temp_revenue";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@CMSCenter", CMSCenter);
            cmd.Connection = con;
            SqlTransaction trans = con.BeginTransaction();
            cmd.Transaction = trans;
            try
            {
                nr = cmd.ExecuteNonQuery();
                trans.Commit();
            }
            catch (SqlException se)
            {
                trans.Rollback();
            }
            disconnect();
        }
           
        internal void Update_Rounding_Totals(string uCMS_Center, string inv_nber, float TOTAL_PROC,float TOTAL_PHAR,float TOTAL_PAY,float VAT_AMOUNT)
        {
            string sql = "UPDATE temp_revenue "
                        + " SET TOTAL_PROC=" + TOTAL_PROC + ", TOTAL_PHAR=" + TOTAL_PHAR + ", "
                        + " TOTAL_PAY=" + TOTAL_PAY + ", VAT_AMOUNT=" + VAT_AMOUNT
                        + " WHERE CMS_Center='" + uCMS_Center + "' AND INV_NBER='" + inv_nber + "'";            
            executeNonquery(sql);
 	    }

        internal void Update_pays_temp_proc_phar(string CMSCenter)
        {
            if (connect() == false)
                return;
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "sp_update_pays_temp_proc_phar";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@CMSCenter", CMSCenter);
            cmd.Connection = con;
            SqlTransaction trans = con.BeginTransaction();
            cmd.Transaction = trans;
            try
            {
                cmd.ExecuteNonQuery();
                trans.Commit();
            }
            catch (SqlException se)
            {
                trans.Rollback();
            }
            disconnect();
        }

        internal void Update_Rounding_Temp_proc_phar(string uCMS_Center, string uInv_nber, string uProc_phar_code, string uProc_phar_nber, float PROC_PHAR_PAY)
        {
            string SQLStr = "UPDATE temp_Proc_Phar"
                           + " SET PROC_PHAR_PAY=" + PROC_PHAR_PAY
                           + " WHERE CMS_Center='" + uCMS_Center + "'"
                           + " AND INV_NBER='" + uInv_nber + "'"
                           + " AND PROC_PHAR_CODE='" + uProc_phar_code + "'"
                           + " AND PROC_PHAR_NBER='" + uProc_phar_nber + "'";
            executeNonquery(SQLStr);
        }

        //================================================
        public int update_DailyTrans(string date_visit, string cms_nber, string vat_nber_revised,
                                int received, string upmode_revised, string received_date,
                                string rcpt_name, string upd_remark)
        {
            if (connect() == false)
                return 0;
            int nr = 0;
            SqlCommand Upcmd = new SqlCommand();
            Upcmd.CommandType = CommandType.StoredProcedure;
            Upcmd.CommandText = "sp_update_Daily_Trans_datevisit";

            Upcmd.Parameters.AddWithValue("@date_visit", date_visit);
            Upcmd.Parameters.AddWithValue("@CMS_number", cms_nber);
            Upcmd.Parameters.AddWithValue("@VAT_number_revised", vat_nber_revised);
            Upcmd.Parameters.AddWithValue("@recevied", received);
            Upcmd.Parameters.AddWithValue("@upd_paymode_revised", upmode_revised);
            Upcmd.Parameters.AddWithValue("@received_date", received_date);
            Upcmd.Parameters.AddWithValue("@rcpt_name", rcpt_name);
            Upcmd.Parameters.AddWithValue("@upd_remark", upd_remark);

            Upcmd.Parameters["@date_visit"].DbType = DbType.DateTime;
            Upcmd.Parameters["@received_date"].DbType = DbType.DateTime;
            Upcmd.Parameters["@recevied"].DbType = DbType.Byte;
            Upcmd.Connection = con;
            SqlTransaction trans = con.BeginTransaction();
            Upcmd.Transaction = trans;
            try
            {
                nr = Upcmd.ExecuteNonQuery();
                trans.Commit();
            }
            catch (SqlException se)
            {
                trans.Rollback();
            }     
            disconnect();
            return nr;
        }

        /*public void runDTS(string SQLcode )
        {
            if (connect() == false)
                return;
             try
            {
                DTS.Package p = new DTS.Package();
                object pVarPersistStgOfHost = null;
                
                p.LoadFromSQLServer("local", "", "", DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, "", "", "", "New_Revenue", ref pVarPersistStgOfHost);
                 
                p.Execute();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }*/

        internal void usp_InsertIntoDailyTransTemp(string dfrom, string dto)
        {
            if (connect() == false)
                return;
            int nr = 0;
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "usp_InsertIntoDailyTransTemp";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@sdate", dfrom);
            cmd.Parameters.AddWithValue("@edate", dto);
            cmd.Parameters["@sdate"].DbType = DbType.DateTime;
            cmd.Parameters["@edate"].DbType = DbType.DateTime;
            cmd.Connection = con;
            SqlTransaction trans = con.BeginTransaction();
            cmd.Transaction = trans;
            try
            {
                nr = cmd.ExecuteNonQuery();
                trans.Commit();
            }
            catch (SqlException se)
            {
                trans.Rollback();
            }
            disconnect();
        }

        internal void usp_InsertIntoDailyTrans(string dfrom, string dto)
        {
            if (connect() == false)
                return;
            int nr = 0;
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "usp_InsertIntoDailyTrans";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@sdate", dfrom);
            cmd.Parameters.AddWithValue("@edate", dto);
            cmd.Parameters["@sdate"].DbType = DbType.DateTime;
            cmd.Parameters["@edate"].DbType = DbType.DateTime;
            cmd.Connection = con;
            SqlTransaction trans = con.BeginTransaction();
            cmd.Transaction = trans;
            try
            {
                nr = cmd.ExecuteNonQuery();
                trans.Commit();
            }
            catch (SqlException se)
            {
                trans.Rollback();
            }
            disconnect();
        }

        internal int InsertIntoDailyTransReport(string pStartnum, string pEndnum, string pVATcenter)
        {
            if (connect() == false)
                return 0;
            int nr = 0;
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "usp_InsertIntoDailyTransReport";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@sinv", pStartnum);
            cmd.Parameters.AddWithValue("@einv", pEndnum);
            cmd.Parameters.AddWithValue("@vat_center", pVATcenter);            
            cmd.Connection = con;
            SqlTransaction trans = con.BeginTransaction();
            cmd.Transaction = trans;
            try
            {
                nr = cmd.ExecuteNonQuery();
                trans.Commit();
            }
            catch (SqlException se)
            {
                trans.Rollback();
            }
            disconnect();
            return nr;
        }

        internal int usp_Insert_AB_ACCOUNT_CODE(string kind_pment, string acc_deb,
            string acc_name, string acc_cre_med, string acc_cre_serv, string acc_cre_drug,
            string acc_cre_disp, string t0_code, string t4_code, string kind_pment_desc)
        {
            if (connect() == false)
                return 0;
            int nr = 0;
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "usp_Insert_AB_ACCOUNT_CODE";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@KIND_PMENT", kind_pment);
            cmd.Parameters.AddWithValue("@ACC_DEB", acc_deb);
            cmd.Parameters.AddWithValue("@ACC_NAME", acc_name);
            cmd.Parameters.AddWithValue("@ACC_CRE_MED", acc_cre_med);
            cmd.Parameters.AddWithValue("@ACC_CRE_SERV", acc_cre_serv);
            cmd.Parameters.AddWithValue("@ACC_CRE_DRUG", acc_cre_drug);
            cmd.Parameters.AddWithValue("@ACC_CRE_DISP", acc_cre_disp);
            cmd.Parameters.AddWithValue("@T0_CODE", t0_code);
            cmd.Parameters.AddWithValue("@T4_CODE", t4_code);
            cmd.Parameters.AddWithValue("@KIND_PMENT_DESC", kind_pment_desc);
            //cmd.Parameters["@sdate"].DbType = DbType.DateTime;
            //cmd.Parameters["@edate"].DbType = DbType.DateTime;
            cmd.Connection = con;
            SqlTransaction trans = con.BeginTransaction();
            cmd.Transaction = trans;
            try
            {
                nr = cmd.ExecuteNonQuery();
                trans.Commit();
            }
            catch (SqlException se)
            {
                trans.Rollback();
            }
            disconnect();
            return nr;
        }

        internal int usp_Delete_AB_ACCOUNT_CODE(string kind_pment)
        {
            if (connect() == false)
                return 0;
            int nr = 0;
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "usp_Delete_AB_ACCOUNT_CODE";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@KIND_PMENT", kind_pment);
            //cmd.Parameters["@sdate"].DbType = DbType.DateTime;
            //cmd.Parameters["@edate"].DbType = DbType.DateTime;
            cmd.Connection = con;
            SqlTransaction trans = con.BeginTransaction();
            cmd.Transaction = trans;
            try
            {
                nr = cmd.ExecuteNonQuery();
                trans.Commit();
            }
            catch (SqlException se)
            {
                trans.Rollback();
            }
            disconnect();
            return nr;
        }

        internal DataTable get_Invoice(string vatCenter, string vatNum)
        {
            string sql = "Select * from NEW_Revenue "
                        + " where UPD_VAT_Num = right(('00000' + cast('" + vatNum + "' as varchar(5))),5)"
                        + " AND UPD_VAT_Center = '" + vatCenter + "'";            

            return executeQuery(sql);
        }

        internal DataTable get_Reason_Type()
        {
            string sql = "SELECT DISTINCT tVoid_Invoice_Reason_Type.Reason_Type, tVoid_Invoice_Reason_Type.Reason_ID"
                       + " FROM tVoid_Invoice_Reason_Type"
                       + " ORDER BY tVoid_Invoice_Reason_Type.Reason_ID ";
            return executeQuery(sql);
        }

        internal DataTable get_Description(string pReasonID)
        {
            string sql = "SELECT tVoid_Invoice_Reason.Reason_Des, tVoid_Invoice_Reason.Reason_ID "
                        + " FROM tVoid_Invoice_Reason"
                        + " WHERE Reason_ID='" + pReasonID + "'"
                        + " ORDER BY Reason_Des";

            return executeQuery(sql);
        }

        internal void update_Invoice_VOID(string vatcenter, string vatnum, string upd_acctcode, string upd_inv_stat, 
                                            string upd_pmnt_date, string upd_remark,string upd_reason, string upd_reason_type)
        {
            string sql = "Update NEW_REVENUE "
                        + " SET UPD_PAY_ACCTCODE='" + upd_acctcode + "',"
                        + " UPD_INV_STAT='" + upd_inv_stat + "',"
                        + " UPD_PAYMENT_DATE='" + upd_pmnt_date + "',"
                        + " UPD_REMARK='" + upd_remark + "'";
            if (upd_remark != "")
                sql += " ,UPD_REASON='" + upd_reason + "'";
            if (upd_reason_type != "")
                sql += " ,UPD_REASON_TYPE='" + upd_reason_type + "'";

            sql += " WHERE UPD_VAT_Num='" + vatnum + "' AND UPD_VAT_Center='" + vatcenter + "'";
            executeNonquery(sql);

        }

        internal DataTable get_Proc_Phar(string uCMS_INV_Nber)
        {
            string sql = "Select * from NEW_Proc_Phar where INV_NBer = '" + uCMS_INV_Nber.Trim() + "'";
            return executeQuery(sql);
        }

        internal int insert_CMS_Checking(string cmsnum,string track_date)
        {
            if (connect() == false)
                return 0;
            int nr = 0;
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "sp_Append_cms_seq_missing";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@cms_number", cmsnum);
            cmd.Parameters.AddWithValue("@track_date", track_date);
            cmd.Parameters["@track_date"].SqlDbType = SqlDbType.DateTime;
            cmd.Connection = con;
            SqlTransaction trans = con.BeginTransaction();
            cmd.Transaction = trans;
            try
            {
                nr = cmd.ExecuteNonQuery();
                trans.Commit();
            }
            catch (SqlException se)
            {
                trans.Rollback();
            }
            disconnect();
            return nr;
        }

        internal DataTable get_Temp_proc_phar()
        {
            string sql = @"SELECT temp_Proc_Phar.CMS_Center, temp_Proc_Phar.INV_NBER, temp_Proc_Phar.DATE_VISIT, temp_Proc_Phar.UPD_DOC_CODE, temp_Proc_Phar.PAT_FILCOD, temp_Proc_Phar.PROC_PHAR_NBER, temp_Proc_Phar.PROC_PHAR_IND, temp_Proc_Phar.PROC_PHAR_CODE, temp_Proc_Phar.PROC_PHAR_NAME, temp_Proc_Phar.PROC_PHAR_TYPE, temp_Proc_Phar.PROC_PHAR_X, temp_Proc_Phar.PROC_PHAR_PAY, temp_Proc_Phar.INV_STAT, temp_Proc_Phar.UPD_DOC_ALIAS, temp_Proc_Phar.UPD_INV_STAT, temp_Proc_Phar.UPD_SUN_T2, temp_Proc_Phar.UPD_STAT_Cat
                            FROM temp_Proc_Phar
                            ORDER BY temp_Proc_Phar.CMS_Center, temp_Proc_Phar.INV_NBER, temp_Proc_Phar.DATE_VISIT, temp_Proc_Phar.UPD_DOC_CODE DESC";
            return executeQuery(sql);
        }

        internal void update_temp_proc_phar(string pCMSCenter, string pDatevisit, string pInvNber, string pProc_phar_code, 
                                            string pProc_phar_nber, string pUpd_doc_code, string pUpd_Doc_Alias)
        {
            string sql = "UPDATE temp_Proc_Phar "
                        + " SET UPD_DOC_CODE = '" + pUpd_doc_code + "',"
                        + " UPD_DOC_ALIAS = '" + pUpd_Doc_Alias + "'"
                        + " WHERE CMS_Center='" + pCMSCenter + "' AND DATE_VISIT= '" + pDatevisit + "'"
                        + " AND INV_NBER='" + pInvNber + "' AND PROC_PHAR_CODE='" + pProc_phar_code + "'"
                        + " AND PROC_PHAR_NBER='" + pProc_phar_nber + "'";
            executeNonquery(sql);
        }

        internal int usp_Update_AB_ACCOUNT_CODE(string kind_pment, string acc_deb, string acc_name,
            string acc_cre_med, string acc_cre_serv, string acc_cre_drug, string acc_cre_disp,
            string t0_code, string t4_code, string kind_pment_desc)
        {
            if (connect() == false)
                return 0;
            int nr = 0;
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "usp_Update_AB_ACCOUNT_CODE";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@KIND_PMENT", kind_pment);
            cmd.Parameters.AddWithValue("@ACC_DEB", acc_deb);
            cmd.Parameters.AddWithValue("@ACC_NAME", acc_name);
            cmd.Parameters.AddWithValue("@ACC_CRE_MED", acc_cre_med);
            cmd.Parameters.AddWithValue("@ACC_CRE_SERV", acc_cre_serv);
            cmd.Parameters.AddWithValue("@ACC_CRE_DRUG", acc_cre_drug);
            cmd.Parameters.AddWithValue("@ACC_CRE_DISP", acc_cre_disp);
            cmd.Parameters.AddWithValue("@T0_CODE", t0_code);
            cmd.Parameters.AddWithValue("@T4_CODE", t4_code);
            cmd.Parameters.AddWithValue("@KIND_PMENT_DESC", kind_pment_desc);
            //cmd.Parameters["@sdate"].DbType = DbType.DateTime;
            //cmd.Parameters["@edate"].DbType = DbType.DateTime;
            cmd.Connection = con;
            SqlTransaction trans = con.BeginTransaction();
            cmd.Transaction = trans;
            try
            {
                nr = cmd.ExecuteNonQuery();
                trans.Commit();
            }
            catch (SqlException se)
            {
                trans.Rollback();
            }
            disconnect();
            return nr;
        }
    }
}
